\documentclass{article}

\usepackage[french]{babel}
\usepackage[utf8]{inputenc}
\usepackage{graphicx}


%%%%%%%%%%%%%%%% Lengths %%%%%%%%%%%%%%%%
\setlength{\textwidth}{15.5cm}
\setlength{\evensidemargin}{0.5cm}
\setlength{\oddsidemargin}{0.5cm}

%%%%%%%%%%%%%%%% Variables %%%%%%%%%%%%%%%%
\def\projet{Projet SGBD}

\def\equipe{489} \def\membres{Gardon Henri, Diebold Joffrey, Pocquet du Haut Jussé Pierre-Alain}
\def\encadrant{Sylvain Lombardy}
\title{Projet de gestion d'un club sportif}
\begin{document}

%%%%%%%%%%%%%%%% Header %%%%%%%%%%%%%%%%

  \begin{figure}[h!]
		\begin{center}
                        \fbox{\begin{tabular}{l}
                            {\bfseries \sffamily
                              \projet} \\
                            {\bfseries \sffamily
                              Membres :} \membres \\ 
                            {\bfseries \sffamily
                              Encadrant :} \encadrant \\
                        \end{tabular}}
			\title{
			\textbf{Projet de système de gestion de base de données}}                     
			\maketitle
         \includegraphics[scale=0.4]{logo-enseirb.jpg}\\
			ENSEIRB-MATMECA\\
			1 Avenue du docteur Albert Schweitzer \\
			33402 Talence\\
                        \noindent    
                        \vskip 0mm
                        \noindent
                        \vskip 4mm ~
		\end{center}
  \end{figure}
  

  \newpage

  \tableofcontents
  \newpage

\section*{Introduction}
\addcontentsline{toc}{section}{Introduction}


\paragraph{Rappel du sujet :}
Ce projet consiste à réaliser une base de données gérant les rencontres de basket-ball entre les clubs d’une fédération. On dispose des informations suivantes :

\begin{itemize}

\item Un club de basket possède un bureau (les responsables) formé d'un président, d'un viceprésident, d'un trésorier, d'un secrétaire, etc.

\item Au sein d'un même club, il existe plusieurs catégories (ex : junior, cadet, benjamin, etc.). Pour une catégorie, il peut exister plusieurs équipes dans le même club (ex : cadet I, cadet II).

\item Chaque équipe est composée de plusieurs joueurs. Une équipe a au moins un entraîneur.

\item Un joueur a numéro de licence, un nom, un prénom, une date de naissance, une adresse. On veut aussi enregistrer la date de son entrée dans le club, les cumuls de points marqués et de fautes (depuis le début de la saison). Un joueur peut changer d'équipe.
\item Pour un entraîneur, on enregistre son nom, son prénom et sa date d'entrée au club. Il peut entraîner plusieurs équipes du club.
\item Une rencontre est caractérisée par une date et un numéro et se déroule entre deux équipes. A la fin de la rencontre, on enregistre les deux scores des équipes.

\end{itemize}

On veut stocker toutes ces informations dans la base, et pouvoir disposer des requêtes de service suivantes :

\begin{description}

\item[Consultation :]
  
  \begin{itemize}
    
  \item Informations sur les clubs, les équipes, les joueurs. Les scores des matchs joués à une date donnée, la liste des joueurs à une date donnée, la feuille du match à une date donnée, etc. Pour un club, on veut aussi le nombre de matchs gagnés, perdus ou nuls ...
    
\end{itemize} 

\item [Statistiques :]

  \begin{itemize}
   
  \item moyenne des points marqués par rencontre à une date donnée,
  \item moyenne des points marqués depuis le début de la saison,
  \item classement des meilleurs joueurs d'une journée pour une catégorie,
  \item classement des équipes.

\end{itemize}

\item [Mise à jour :]


  \begin{itemize}
    
  \item Ajout, suppression, modification d'un joueur, d'un club, d'un match. 
    
  \end{itemize}
  
\end{description} 

Le but de ce projet est de développer une base de donnée permettant
la gestion d'un club sportif, ici de basket. Le système se veut le plus ergonomique possible, ainsi les utilisateurs pourront obtenir les informations esssentielles sur les joueurs inscrits, sur les clubs, les équipes, les rencontres effectuées et pourront consulter cette base de données pour afficher différent classements.




\section{Modèle conceptuel}
La lecture de l'énoncé nous a permis d'identifier et de construire le schéma entité-association suivant \ref{fig:ent-assoc}. 

\newpage
\begin{figure}
  \center
  \includegraphics[scale=0.6]{schema-entites.png}
  \caption{Schéma entité-association de notre projet.}
  \label{fig:ent-assoc}
\end{figure}

Nous avons mis en place un héritage entre l'entité \texttt{Personne} et les différentes entités hérités \texttt{Joueur} \texttt{Entraineur} et \texttt{Membre\_Bureau} afin d'éviter la redondance de champs.
A cela on ajoute des entités  \texttt{Club},  \texttt{Catégorie},  \texttt{Equipe} et  \texttt{Rencontre} .
\\L'entité \texttt{Rencontre} nous permet de conserver les scores des matches ainsi que les points marqués et les fautes commises par chaque joueur au travers de l'association \texttt{Participe}.
Nous avons ajouté comme hypothèses tous les éléments suivant :
\begin{itemize}
\item
Une rencontre est joué au minimum par 10 joueurs, et ceci sans maximum
\item
Une personne prend part à un seul club
\item
Un joueur peut être en même temps entraîneur et membre du bureau, une personne peut donc avoir plusieurs postes.
\end{itemize}

\section{Modèle relationnel en troisième forme normale}

On obtient finalement le modèle relationnel suivant en troisième forme normale \ref{fig:relation}.
\newpage
\begin{figure}
  \center
  \includegraphics[scale=0.55]{schema-relationnel.png}
  \caption{Modèle relationnel du projet.}
  \label{fig:relation}
\end{figure}


\section{Requêtes SQL}

Nous avons choisi d'implémenter la base SQL à l'aide de phpmyadmin, la création de la base et des données \og à la main \fg n'étant pas un problème en soi, mais cet outil de haut niveau nous a évité des opérations fastidieuses lorsqu'il s'agissait de revenir sur notre schéma relationnel, ou pour faciliter nos tests.

\textbf{IMPORTANT} : nous avons créé un certain nombre de rencontres qui n'ont pas été jouées. Ainsi nous avons créé des rencontres jouées par des SENIORS aux dates du 04/12/2012 et du 11/12/2012 et par des JUNIORS à la date du 11/12/2012. Ainsi, si vous voulez tester des requêtes sur les rencontres, veuillez prendre celles-ci.

\subsection{Clubs}
Nous avons créé 3 clubs différents:
\begin{itemize}
\item
AS EPO
\item
ENSEIRB
\item
LES CADORS
\end{itemize}


Afin de sélectionner l'ensemble des clubs existants il suffit d'appeler select * sur la table CLUB, d'où:
\begin{verbatim}
select * from CLUB
\end{verbatim}

\subsection{Equipes}
Au total 18 équipes ont été créées.
Il y a 6 équipes par club. Celles-ci sont réparties dans 3 catégories différentes (Poussins, Juniors, Séniors).
\\
La requête pour afficher l'ensemble des équipes existantes se fait à l'aide des tables EQUIPE, CATEGORIE et CLUB
\\

\begin{verbatim}
select ID_EQUIPE, NOM_EQUIPE, NOM_CATEGORIE, NOM_CLUB
from EQUIPE, CATEGORIE, CLUB
where EQUIPE.ID_CAT = CATEGORIE.ID_CAT
and CATEGORIE.ID_CLUB = CLUB.ID_CLUB
\end{verbatim}

\subsection{Joueurs}
Chaque équipe comporte 5 joueurs par défaut, il y a ainsi 90 joueurs en tout, plus ou moins 2-3 joueurs que l'on utilise pour tester nos requêtes d'ajout, de modification, et de supression.

La requête pour afficher tous les joueurs existants est la suivante: 
\\
\begin{verbatim}
select JOUEUR.NUMERO_LICENCE, PERSONNE.PRENOM, PERSONNE.NOM, JOUEUR.DATE_NAISSANCE, 
JOUEUR.ADRESSE, CLUB.NOM_CLUB, EQUIPE.NOM_EQUIPE
from JOUEUR, PERSONNE, CLUB, EQUIPE, PARTICIPE
where (PERSONNE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
and (EQUIPE.ID_EQUIPE = JOUEUR.ID_EQUIPE)
group by JOUEUR.NUMERO_LICENCE
order by JOUEUR.NUMERO_LICENCE
\end{verbatim}

Les joueurs sont affichés par numéro de licence croissant. Seuls les joueurs qui font partie d'une équipe sont affichés.

Pour afficher les joueurs ayant participé à au moins une rencontre, on exécute la requête suivante :

\begin{verbatim}
select JOUEUR.NUMERO_LICENCE, PERSONNE.PRENOM, PERSONNE.NOM, JOUEUR.DATE_NAISSANCE,
JOUEUR.ADRESSE, CLUB.NOM_CLUB, EQUIPE.NOM_EQUIPE, SUM(PARTICIPE.POINTS_RENCONTRE) AS S, 
SUM(PARTICIPE.FAUTES_RENCONTRE) AS F
from JOUEUR, PERSONNE, CLUB, EQUIPE, PARTICIPE
where (PERSONNE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
and (EQUIPE.ID_EQUIPE = JOUEUR.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PARTICIPE.NUMERO_LICENCE)
group by JOUEUR.NUMERO_LICENCE
order by JOUEUR.NUMERO_LICENCE
\end{verbatim}

\subsection{Classement des meilleurs marqueurs}

\begin{verbatim}
select PERSONNE.PRENOM, PERSONNE.NOM, CLUB.NOM_CLUB, EQUIPE.NOM_EQUIPE,
SUM(PARTICIPE.POINTS_RENCONTRE) AS S
from JOUEUR, PERSONNE, CLUB, EQUIPE, PARTICIPE
where (PERSONNE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
and (EQUIPE.ID_EQUIPE = JOUEUR.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PARTICIPE.NUMERO_LICENCE)
group by JOUEUR.NUMERO_LICENCE
order by S DESC
\end{verbatim}

Pour le classement des meilleurs marqueurs on sélectionne le nombre total de points marqués lors des rencontres, puis on effectue un "group by" sur le numéro de licence des joueurs afin d'avoir le nombre total de points qu'a inscrit chaque joueur. "Order by S desc" permet de classer ces résultats par ordre décroissant de points marqués.

\subsection{Moyenne de points marqués par journée par rencontre}

\begin{verbatim}
select AVG(2*S)
from
(select EQUIPE.ID_EQUIPE, SUM(PARTICIPE.POINTS_RENCONTRE) AS S, DATE_RENCONTRE
from CLUB, EQUIPE, PARTICIPE, RENCONTRE, JOUEUR, PERSONNE
where (RENCONTRE.DATE_RENCONTRE = 'date voulue'
and (RENCONTRE.ID_RENCONTRE = PARTICIPE.ID_RENCONTRE)
and (PARTICIPE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (JOUEUR.ID_EQUIPE = EQUIPE.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PERSONNE.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
group by EQUIPE.ID_EQUIPE) AS T
\end{verbatim}
La moyenne des points marqués par rencontre pour une journée est obtenue en sélectionnant d'abord les scores de toutes les équipes lors de la journée. Ensuite on effectue une requête imbriquée sur cette dernière requête, où l'on sélectionne la moyenne des scores obtenus multipliés par 2 car on veut obtenir la moyenne par rencontre et non par équipe.

\subsection{Moyenne de points marqués par rencontre à partir d'une date donnée}
\begin{verbatim}
select AVG(2*S)
from
(select EQUIPE.ID_EQUIPE, SUM(PARTICIPE.POINTS_RENCONTRE) AS S, DATE_RENCONTRE
from CLUB, EQUIPE, PARTICIPE, RENCONTRE, JOUEUR, PERSONNE
where (RENCONTRE.DATE_RENCONTRE >= 'date voulue')
and (RENCONTRE.ID_RENCONTRE = PARTICIPE.ID_RENCONTRE)
and (PARTICIPE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (JOUEUR.ID_EQUIPE = EQUIPE.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PERSONNE.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
group by EQUIPE.ID_EQUIPE, RENCONTRE.ID_RENCONTRE) AS T
\end{verbatim}
Pour la moyenne des points marqués par rencontre à partir d'une date donnée (le début de la saison par exemple), on reprend la requête précédente en mettant un $\ge$ devant la date voulue.

\subsection{Classement des meilleurs joueurs d’une journée pour une catégorie}

\begin{verbatim}
select PERSONNE.PRENOM, PERSONNE.NOM, CLUB.NOM_CLUB, EQUIPE.NOM_EQUIPE,
SUM(PARTICIPE.POINTS_RENCONTRE) AS S
from JOUEUR, PERSONNE, CLUB, EQUIPE, PARTICIPE, RENCONTRE, CATEGORIE
where (PERSONNE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
and (EQUIPE.ID_EQUIPE = JOUEUR.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PARTICIPE.NUMERO_LICENCE)
and (PARTICIPE.ID_RENCONTRE = RENCONTRE.ID_RENCONTRE)
and (EQUIPE.ID_CAT = CATEGORIE.ID_CAT)
and (RENCONTRE.DATE_RENCONTRE = 'date voulue')
and (CATEGORIE.NOM_CATEGORIE = 'nom de la catégorie voulue')
group by JOUEUR.NUMERO_LICENCE
order by S DESC
\end{verbatim}

Le principe est ici le même que pour le classement global des joueurs, on choisit juste la date et la catégorie avec des \og where \fg.

\subsection{Score d'une rencontre}

\begin{verbatim}
select NOM_CLUB, NOM_EQUIPE, SUM(PARTICIPE.POINTS_RENCONTRE) as S, DATE_RENCONTRE
from CLUB, EQUIPE, PARTICIPE, RENCONTRE, JOUEUR, PERSONNE
where (RENCONTRE.ID_RENCONTRE = 'id voulu')
and (PARTICIPE.ID_RENCONTRE = RENCONTRE.ID_RENCONTRE)
and (PARTICIPE.NUMERO_LICENCE = JOUEUR.NUMERO_LICENCE)
and (JOUEUR.ID_EQUIPE = EQUIPE.ID_EQUIPE)
and (JOUEUR.NUMERO_LICENCE = PERSONNE.NUMERO_LICENCE)
and (PERSONNE.ID_CLUB = CLUB.ID_CLUB)
and RENCONTRE.ID_EQUIPE1 = JOUEUR.ID_EQUIPE
\end{verbatim}
Le score d'une rencontre donnée pour une des équipes s'effectue avec la requête ci-dessus, une deuxième requête étant effectuée pour l'autre équipe (les champs ID\_Equipe1 et ID\_Equipe2 de l'entité Rencontre sont respectivement pris en compte).

\subsection{Bilan d'une équipe}

\begin{verbatim}
select count(*) as G from
((select ID_RENCONTRE
from RENCONTRE R, EQUIPE E
where E.ID_EQUIPE='id voulue'
and R.ID_EQUIPE1=E.ID_EQUIPE
and R.SCORE1>R.SCORE2)
UNION
(select ID_RENCONTRE
from RENCONTRE R, EQUIPE E
where E.ID_EQUIPE='id voulue'(la même que plus haut)
and R.ID_EQUIPE2=E.ID_EQUIPE
and R.SCORE2>R.SCORE1)) AS T
\end{verbatim}

Pour obtenir le nombre de matches gagnés d'une équipe, on effectue un count(*) sur une requête qui va lister les ID des rencontres gagnées. Pour cela on compare les champs Score1 et Score2 de l'entité Rencontre, en prenant en compte les cas où l'ID de l'équipe en question est l'ID1 ou l'ID2 de la rencontre (rôle du UNION). Les requêtes pour le nombre de matches nuls ou perdus sont semblables.

\subsection{Ajout d'un joueur}

Nous avons décidé de focaliser les opérations de mise à jour sur les joueurs, le principe étant similaire pour d'autres entités.

L'ajout d'un joueur s'effectue en trois temps :

\begin{verbatim}
insert into PERSONNE
VALUES (NULL,'nom voulu','prénom voulu','date d'entrée au club voulue','ID_Club voulu')
\end{verbatim}

D'abord on ajoute une ligne à la table Personne, afin entre autres d'attribuer un numéro de licence à cette personne, et donc au futur joueur.

\begin{verbatim}
select max(NUMERO_LICENCE) from PERSONNE
\end{verbatim}

Ensuite, on selectionne le maximum parmi les numéros de licence, qui sera à coup sûr celui de la personne créée car les clés primaires fonctionnent en auto\_increment.

\begin{verbatim}
insert into JOUEUR
VALUES ('max(NUMERO_LICENCE)','date de naissance voulue','adresse voulue','ID_Equipe voulu')
\end{verbatim}

Enfin, on insère une ligne dans la table Joueur, avec les informations nécessaires.

\subsection{Modification d'un joueur}

Pour modifier un joueur, on effectue un UPDATE sur les tables Joueur et Personne :

\begin{verbatim}
UPDATE JOUEUR
SET DATE_NAISSANCE = 'date de naissance voulue',
ADRESSE = 'adresse voulue',
ID_EQUIPE = 'ID_Equipe voulue'
WHERE JOUEUR.NUMERO_LICENCE ='numéro de licence sélectionné'
\end{verbatim}

\begin{verbatim}
UPDATE PERSONNE 
SET NOM = 'nom voulu',
PRENOM = 'prénom voulu',
DATE_ENTREE_AU_CLUB = 'date d'entrée voulue',
ID_CLUB = 'ID_Club voulu' 
WHERE PERSONNE.NUMERO_LICENCE ='numéro de licence sélectionné'
\end{verbatim}

\subsection{Suppression d'un joueur}

Nous avons décidé que la suppression d'un joueur, pour un souci de cohérence, ne serait pas une suppression au sens strict du terme mais une modification des champs ID\_Equipe de l'entité Joueur et ID\_Club de l'entité Personne, ces deux champs prenant la valeur NULL.

\begin{verbatim}
update JOUEUR
set ID_EQUIPE=NULL
where NUMERO_LICENCE='numéro de licence sélectionné'
\end{verbatim}

\begin{verbatim}
update PERSONNE
set ID_CLUB=NULL
where NUMERO_LICENCE='numéro de licence sélectionné'
\end{verbatim}

\section{Interface graphique}

Nous avons choisi de réaliser une interface assez primaire, codée en HTML et en PHP, qui a pour unique but de pouvoir accéder aux différentes requêtes demandées de manière plutôt ludique. 

En cliquant sur les liens présents sur la page d'accueil on a un accès direct aux différentes informations voulues.

Voici les fichiers php et les accès via l'interface correspondant aux différentes requêtes :

\begin{tabular}{ |p{0.3\textwidth} |p{0.2\textwidth} |p{0.5\textwidth}| }
  \hline
   \textbf{Requête} & \textbf{Fichier php} & \textbf{Accès au résultat dans l'interface depuis l'accueil} \\ \hline
   Clubs & clubs.php & Lien \og Les clubs \fg \\ \hline
   Equipes & equipes.php & Lien \og Les équipes \fg \\ \hline
   Joueurs & joueurs.php et joueurs-participants.php & Liens \og Tous les joueurs \fg et \og Les joueurs ayant participé à au moins une rencontre \fg \\ \hline
   Classement des meilleurs marqueurs & classement\_point.php & Lien \og Classement des joueurs par points marqués \fg \\ \hline
   Moyenne de points marqués par rencontre pour une journée & statistiques-cible.php & Lien \og Statistiques \fg \\ \hline
   Moyenne de points marqués par rencontre depuis le début de la saison & statistiques-cible.php & Lien \og Statistiques \fg \\ \hline
   Classement des meilleurs joueur d'une journée pour une catégorie & rencontres-cibles.php & Lien \og Les rencontres \fg $\rightarrow$ sélectionner une rencontre $\rightarrow$ Valider \\ \hline
   Score d'une rencontre & feuille-de-match.php & Lien \og Les rencontres \fg $\rightarrow$ sélectionner une rencontre $\rightarrow$ Valider $\rightarrow$ cliquer sur le lien d'une rencontre \\ \hline
   Bilan d'une équipe & equipes-bilan.php & Lien \og Les équipes \fg $\rightarrow$ cliquez sur l'ID d'une équipe \\ \hline
   Ajout d'un joueur & ajout-joueur-cible.php & Lien \og Ajouter un joueur\fg \\ \hline
   Modification d'un joueur & modification-joueur-resultat.php & Lien \og Modifier un joueur\fg \\ \hline
   Suppression d'un joueur & suppression-joueur-cible.php & Lien \og Supprimer un joueur\fg \\ \hline

 \end{tabular}

\section{Installation}

Pour installer la base de données, vous devez utiliser un serveur MySQL.

\begin{enumerate}
\item
Exécutez sur votre serveur le fichier "club\_basket.sql". Il installera la base "club\_basket", les structures des tables, toutes les données ainsi qu'un utilisateur prédéfini qui sera appelé lors de chaque connexion à la base via PHP.
\item
Déplacez les fichiers php dans le répertoire de votre serveur Web.
\item
 Profitez de l'interface.
\end{enumerate}

\section*{Conclusion}
\addcontentsline{toc}{section}{Conclusion}

Ainsi, la version nominale de ce projet a été effectuée, avec une interface certes basique mais pratique qui teste facilement les différentes requêtes.

Nous aurions pu aller plus loin en rendant l'interface plus agréable, mais ce n'était pas vraiment le sujet de ce projet. Par contre, la requête de classement des équipes aurait pu être effectuée, mais nous n'avons pas pu faute de temps, et surtout car nous ne pensions pas au début enregistrer les scores des deux équipes à chaque rencontre. En effet, nous pensions que cela aurait été redondant, mais cela s'avère en fait très pratique pour compter le nombre de victoires, nuls et défaites par équipes, et \textit{a fortiori} pour faire un classement. De plus, enregistrer les scores permet de les fixer et d'éviter que si un joueur part d'un club, les points qu'il a marqués avec celui-ci ne soient plus comptabilisés. A ce sujet, la meilleure solution aurait été en fait d'enregistrer un historique complet de chaque joueur afin de savoir par quel club il est passé et à quel moment. Ce système aurait été optimal pour de nombreuses opérations et aurait permis sans doute de s'affranchir carrément des champs Score1 et Score2 de l'entité Rencontre.

\end{document}
